﻿CREATE VIEW vantage.v_Bridge_20
AS
SELECT     BRIDGE.VantageID, BRIDGE.CompanyID, BRIDGE.UsixID, BRIDGE.LoanClientID, CI.LoanID, BRIDGE.ApplicationNumber, BRIDGE.CompanyRef, 
                      BRIDGE.BranchCodeRef, BRIDGE.LoanProgramRef, BRIDGE.ProductRef, BRIDGE.JurisdictionRef, BRIDGE.PortfolioRef, BRIDGE.DealerName, 
                      BRIDGE.InterestTypeRef, BRIDGE.CurrentStatusRef, BRIDGE.CurrentStatusDate, BRIDGE.CollateralStatusRef, BRIDGE.CollateralStatusDate, 
                      BRIDGE.OriginalAmountFinanced, BRIDGE.AmountFinanced, BRIDGE.OpenDate, BRIDGE.FirstDueDate, BRIDGE.LoanTerm, BRIDGE.OriginalLoanTerm,
                       BRIDGE.OriginalInterestRate, BRIDGE.InterestRate, BRIDGE.OriginalAPR, BRIDGE.APR, BRIDGE.OriginalDueDay, BRIDGE.DueDay, 
                      BRIDGE.OriginalPaymentAmount, BRIDGE.PaymentAmount, BRIDGE.PaymentFrequencyRef, BRIDGE.OriginalFinanceCharge, BRIDGE.FinanceCharge, 
                      BRIDGE.DiscountAmount, BRIDGE.OriginalReserve, BRIDGE.OriginalWithHold, BRIDGE.PurchaseDate, BRIDGE.PurchasePrincipalPaid, 
                      BRIDGE.PurchaseInterest, BRIDGE.PurchaseInterestPaid, BRIDGE.LoanFundingDate, BRIDGE.NextDueDate, BRIDGE.MaturityDate, 
                      BRIDGE.GrossBalance, BRIDGE.InterestAccrued, BRIDGE.InterestPaid, BRIDGE.PrinicpalPaid, BRIDGE.FeePaid, BRIDGE.PastDueInterest, 
                      BRIDGE.CurrentDueInterest, BRIDGE.PastDuePrincipal, BRIDGE.CurrentDuePrincipal, BRIDGE.NSFFeeBalance, BRIDGE.LateFeeBalance, 
                      BRIDGE.LegalFeeBalance, BRIDGE.PaymentMethodFeeBalance, BRIDGE.RepoFeeBalance, BRIDGE.InsuranceFeeBalance, BRIDGE.OtherFeeBalance, 
                      BRIDGE.DefermentFeeBalance, BRIDGE.DueDayChangeFeeBalance, BRIDGE.CollectionFeeBalance, BRIDGE.RecoveryFeeBalance, BRIDGE.Make, 
                      BRIDGE.Model, BRIDGE.ModelYear, BRIDGE.VIN, BRIDGE.CashPrice, BRIDGE.MilageAtPurchase, BRIDGE.GPSTrackerID, BRIDGE.BVAtPurchase, 
                      BRIDGE.Milage, BRIDGE.MilageAsOfDate, BRIDGE.BV, BRIDGE.BVAsOfDate, BRIDGE.TitleStateRef, BRIDGE.IsRegistered, BRIDGE.IsTitleReceived, 
                      BRIDGE.RelationshipTypeRef, BRIDGE.FirstName, BRIDGE.LastName, BRIDGE.FirstName + ' ' + BRIDGE.LastName AS ClientName, BRIDGE.SSN, 
                      BRIDGE.DLN, BRIDGE.DOB, BRIDGE.FICO, BRIDGE.MaritalStatusRef, BRIDGE.Occupation, BRIDGE.PrimaryLanguageRef, BRIDGE.TimeZoneRef, 
                      BRIDGE.EntityStatusRef, BRIDGE.Street, BRIDGE.AptSuite, BRIDGE.City, BRIDGE.CountyRef, BRIDGE.StateRef, BRIDGE.ZipCode, BRIDGE.DPD, 
                      BRIDGE.PastDueAmount, BRIDGE.LastPaymentDate, BRIDGE.DSLP, 
                      BRIDGE.GrossBalance + BRIDGE.PastDueAmount - BRIDGE.PastDuePrincipal AS PayOffAmount, BRIDGE.PaymentsMade, BRIDGE.PaymentsPerYear, 
                      BRIDGE.PrincipalBalance, BRIDGE.PerDiem, BRIDGE.PhoneHome, BRIDGE.PhoneWork, BRIDGE.PhoneMobile, BRIDGE.PhoneOther, DATEDIFF(dd, 
                      NL.NoteDate, GETDATE()) AS DaysSinceLastNote
FROM         vantage.t_NoteLast AS NL RIGHT OUTER JOIN
                      vantage.v_Bridge_10 AS BRIDGE ON NL.AccountNumber = BRIDGE.VantageID FULL OUTER JOIN
                          (SELECT     LoanID, BorrowerID, Pos
                            FROM          dbo.t_Loan_Borrower
                            WHERE      (Pos = 0)) AS L_B INNER JOIN
                      dbo.t_Borrower AS B ON L_B.BorrowerID = B.ID INNER JOIN
                      dbo.t_CarInfo AS CI ON L_B.LoanID = CI.LoanID ON BRIDGE.SSN = B.SSN AND BRIDGE.VIN = CI.VIN
WHERE     (NOT (DATEDIFF(dd, NL.NoteDate, GETDATE()) IS NULL))

GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPane1', @value = N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[26] 4[28] 2[26] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[42] 4[33] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1[29] 4) )"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4) )"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "BRIDGE"
            Begin Extent = 
               Top = 110
               Left = 379
               Bottom = 261
               Right = 592
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "L_B"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 99
               Right = 189
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "B"
            Begin Extent = 
               Top = 6
               Left = 227
               Bottom = 489
               Right = 449
            End
            DisplayFlags = 344
            TopColumn = 0
         End
         Begin Table = "CI"
            Begin Extent = 
               Top = 284
               Left = 130
               Bottom = 392
               Right = 331
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "NL"
            Begin Extent = 
               Top = 6
               Left = 630
               Bottom = 213
               Right = 805
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 114
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Wid', @level0type = N'SCHEMA', @level0name = N'vantage', @level1type = N'VIEW', @level1name = N'v_Bridge_20';


GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPane2', @value = N'th = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 5700
         Alias = 1575
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End', @level0type = N'SCHEMA', @level0name = N'vantage', @level1type = N'VIEW', @level1name = N'v_Bridge_20';


GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPaneCount', @value = 2, @level0type = N'SCHEMA', @level0name = N'vantage', @level1type = N'VIEW', @level1name = N'v_Bridge_20';

